<?php
/**
* @file $Id: StudentSummary.php 510 2007-05-11 23:43:56Z focus-sis $
* @package Focus/SIS
* @copyright Copyright (C) 2006 Andrew Schmadeke. All rights reserved.
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.txt
* Focus/SIS is free software. This version may have been modified pursuant
* to the GNU General Public License, and as distributed it includes or
* is derivative of works licensed under the GNU General Public License or
* other free or open source software licenses.
* See COPYRIGHT.txt for copyright notices and details.
*/

DrawHeader(ProgramTitle());

$user_syear = UserSyear();
$user_school = UserSchool();
$user_student_id = UserStudentID();
$user_profile = User('PROFILE');

//Establish a start date, and store it in $REQUEST['day_start']
if ($_REQUEST['day_start'] 
&& $_REQUEST['month_start'] 
&& $_REQUEST['year_start']) 
{
	while(!VerifyDate($start_date = 
	$_REQUEST['day_start'].'-'
	.$_REQUEST['month_start'].'-'
	.$_REQUEST['year_start'])) 
	{  //this seems to me to be inherantly unsafe, what if its becomes negative?  -BL
		$_REQUEST['day_start']--;
	}
}
else {
	$min_date = DBGet(DBQuery(
			"SELECT      MIN(SCHOOL_DATE) AS MIN_DATE 
			 FROM        ATTENDANCE_CALENDAR 
			 WHERE       SYEAR = '$user_syear' 
			 AND         SCHOOL_ID = '$user_school'"
			));
			
	if ($min_date[1]['MIN_DATE']) {
		$start_date = $min_date[1]['MIN_DATE'];
	}
	else {
		$start_date = '01-'.strtoupper(date('M-y'));
	}
}

//Establish an end date, and store it in $REQUEST['day_end']
if ($_REQUEST['day_start'] 
&& $_REQUEST['month_start'] 
&& $_REQUEST['year_start']) 
{
	while(!VerifyDate($end_date = 
	$_REQUEST['day_end'].'-'
	.$_REQUEST['month_end'].'-'
	.$_REQUEST['year_end'])) 
	{  // again, what if this becomes negative? -BL
		$_REQUEST['day_end']--;
	}	
}
else {
	$end_date = DBDate();
}

if ($user_profile == 'teacher') {
	$_REQUEST['period_id'] = UserPeriod();
}
	
if ($_REQUEST['search_modfunc'] 
|| $user_student_id 
|| $_REQUEST['student_id'] 
|| $user_profile == 'parent' 
|| $user_profile == 'student') 
{
	if (!$user_student_id 
	&& !$_REQUEST['student_id'])
	{
		// What periods are there in this school?
		$periods_RET = DBGet(DBQuery(
				"SELECT      PERIOD_ID
				 ,           TITLE
				 FROM        SCHOOL_PERIODS
				 WHERE       SYEAR = '$user_syear'
				 AND         SCHOOL_ID = '$user_school'
				 ORDER BY    SORT_ORDER"
				));
		
		// build a selection list with each period in the school as an option (or "Daily" for all)
		$period_select = 
				' : <SELECT name="period_id" onchange="this.form.submit();">'
				.'<OPTION value="">'
				._('Daily')
				.'</OPTION>';
				
		foreach($periods_RET as $period) {
			$period_select .= '<OPTION value="'.$period['PERIOD_ID'].'"'
					.(($_REQUEST['period_id'] == $period['PERIOD_ID']) 
						? ' SELECTED' 
						: ''
					 ).'>'
					.$period['TITLE']
					.'</OPTION>';
		}
		
		$period_select .= '</SELECT>';
	}
	
	$PHP_tmp_SELF = PreparePHP_SELF();

	if ($_REQUEST['student_id'] 
	|| $user_student_id) 
	{
		DrawStudentHeader();
	}

	echo '<FORM action="'.$PHP_tmp_SELF.'" method="POST">';

	if (!$user_student_id 
	|| $user_profile == 'admin') 
	{
		DrawHeader(' &nbsp; &nbsp; <B>'._('Report Timeframe').': </B>'
				.PrepareDate($start_date,'_start').' - '
				.PrepareDate($end_date,'_end').$period_select
				,'<INPUT type="submit" value="'._('Go').'">');
	}
	echo '</FORM>';
}

$codes_RET = DBGet(DBQuery(
		"SELECT      ID
		 ,           TITLE
		 ,           STATE_CODE
		 ,           SHORT_NAME
		 FROM        ATTENDANCE_CODES
		 WHERE       SYEAR = '$user_syear'
		 AND         SCHOOL_ID = '$user_school'
		 AND         TABLE_NAME = '0'
		 AND (       DEFAULT_CODE != 'Y'
		 OR          DEFAULT_CODE IS NULL
		     ) 
		 ORDER BY    SORT_ORDER"
		));
		
$codes_shortname_RET = DBGet(DBQuery(
		"SELECT      TITLE
		 ,           SHORT_NAME
		 FROM        ATTENDANCE_CODES
		 WHERE       SYEAR = '$user_syear'
		 AND         SCHOOL_ID = '$user_school'
		 AND         TABLE_NAME = '0'
		 AND (       DEFAULT_CODE != 'Y'
		 OR          DEFAULT_CODE IS NULL
		     )
		 ORDER BY    SORT_ORDER"
		), array(), array('SHORT_NAME'));

if ($_REQUEST['period_id']) {
	$extra['SELECT'] .= 
			",(SELECT      count(*) 
			   FROM        ATTENDANCE_PERIOD ap
			   ,           ATTENDANCE_CODES ac
			   WHERE       ac.ID = ap.ATTENDANCE_CODE
			   AND         ac.STATE_CODE in ('A', 'H')
			   AND         ap.STUDENT_ID = ssm.STUDENT_ID
			   AND         ap.PERIOD_ID = '$_REQUEST[period_id]'
			   AND         ap.SCHOOL_DATE BETWEEN '$start_date' AND '$end_date'
			  ) AS STATE_ABS";
	
	$extra['columns_after']['STATE_ABS'] = _('State Abs');
	
	if (count($codes_RET) > 1)	{
		foreach($codes_RET as $code) {
			$extra['SELECT'] .= 
					",(SELECT      count(*)
					   FROM        ATTENDANCE_PERIOD ap
					   ,           ATTENDANCE_CODES ac
					   WHERE       ac.ID = ap.ATTENDANCE_CODE
					   AND         ac.ID = '".$code['ID']."'
					   AND         ap.PERIOD_ID = '$_REQUEST[period_id]'
					   AND         ap.STUDENT_ID = ssm.STUDENT_ID
					   AND         ap.SCHOOL_DATE BETWEEN '$start_date' AND '$end_date'
					  ) AS ABS_".$code['ID'];
		}
		foreach($codes_RET as $code) {
			$extra['columns_after']['ABS_'.$code['ID']] = $code['TITLE'];	
		}
	}
}
else {
	$extra['SELECT'] = 
			",(SELECT      COALESCE((SUM(STATE_VALUE)),0.0)
			   FROM        ATTENDANCE_DAY ad
			   WHERE       ad.STUDENT_ID = ssm.STUDENT_ID
			   AND         ad.SCHOOL_DATE BETWEEN '$start_date' AND '$end_date'
			  ) AS STATE_ABS";	
			 
	$extra['columns_after']['STATE_ABS'] = _('Total Full-Day Attended');
	
	if (count($codes_RET) > 1) {
		foreach($codes_RET as $code) {
			$extra['SELECT'] .= 
					",(SELECT      count(*) 
					   FROM        ATTENDANCE_PERIOD ap
					   ,           ATTENDANCE_CODES ac 
					   WHERE       ac.ID = ap.ATTENDANCE_CODE
					   AND         ac.ID = '".$code['ID']."' 
					   AND         ap.STUDENT_ID = ssm.STUDENT_ID
					   AND         ap.SCHOOL_DATE BETWEEN '$start_date' AND '$end_date'
					   ) AS ABS_".$code['ID'];
					   
			if ($code['STATE_CODE']=='A') {
				$codes_list .= ",'".$code['ID']."'";
			}
			$extra['columns_after']['ABS_'.$code['ID']] = 
					_('Periods').' '.$code['TITLE'].' ('.$code['SHORT_NAME'].')';	
		}
		$extra['SELECT'] .= 
				",(SELECT      count(*) 
				   FROM        ATTENDANCE_PERIOD ap
				   ,           ATTENDANCE_CODES ac 
				   WHERE       ac.ID = ap.ATTENDANCE_CODE 
				   AND         ac.ID IN (".substr($codes_list,1).") 
				   AND         ap.STUDENT_ID = ssm.STUDENT_ID
				   AND         ap.SCHOOL_DATE BETWEEN '$start_date' AND '$end_date'
				   ) AS ABS_ALL";
				   
		$extra['columns_after']['ABS_ALL'] = _('Total Periods Missed');
	}
}
$extra['link']['FULL_NAME']['link'] = "Modules.php"
		."?modname=$_REQUEST[next_modname]"
		."&day_start=$_REQUEST[day_start]"
		."&day_end=$_REQUEST[day_end]"
		."&month_start=$_REQUEST[month_start]"
		."&month_end=$_REQUEST[month_end]"
		."&year_start=$_REQUEST[year_start]"
		."&year_end=$_REQUEST[year_end]"
		."&period_id=$_REQUEST[period_id]";
		
$extra['link']['FULL_NAME']['variables'] = array('student_id'=>'STUDENT_ID');

if ($_REQUEST['search_modfunc']=='list') {
	$schools = substr(str_replace(",", "','", User('SCHOOLS')), 2, -2);

	if ($_REQUEST['_search_all_schools'] != 'Y') {
		$school_id_condition = "c.SCHOOL_ID = '$user_school'";
	}
	elseif ($schools) {	
		$school_id_condition = "c.SCHOOL_ID IN ( $schools )";
	}
	else {  //yes, I intended this to always be true in this  case
		$school_id_condition = "c.SCHOOL_ID = c.SCHOOL_ID";
	}
	
	$cal_days = DBGet(DBQuery(
			"SELECT      count(*) AS COUNT
			 ,           c.CALENDAR_ID
			 ,           s.TITLE 
			 FROM        ATTENDANCE_CALENDAR c
			 ,           ATTENDANCE_CALENDARS s 
			 WHERE       s.CALENDAR_ID = c.CALENDAR_ID 
			 AND         $school_id_condition 
			 AND         c.SYEAR = '$user_syear' 
			 AND         c.SCHOOL_DATE BETWEEN '$start_date' AND '$end_date' 
			 GROUP BY    c.CALENDAR_ID
			 ,           s.TITLE"
			 ), array(), array('CALENDAR_ID'));

	if (count($cal_days) > 1) {
		$days_content = '<B>'._('Days Possible').':</B><UL>';
		foreach($cal_days as $cal_id=>$count) {
			$days_content .= 
					'<LI>'
					.$count[1]['TITLE'].': '
					.$count[1]['COUNT']
					.'</LI>';
		}
		$days_content .= '</UL>';
	}
	else {
		$days_content = '<B>'._('Days Possible').': </B>'
				.$cal_days[key($cal_days)][1]['COUNT'];
	}
	if (!$user_student_id) {
		DrawHeader('<BR>'.$days_content.'<BR><BR>');
	}
}

Widgets('all');
Search('student_id',$extra);

if ($user_student_id) {
	$name_RET = DBGet(DBQuery(
			"SELECT      FIRST_NAME || ' ' || COALESCE(MIDDLE_NAME,' ') 
							|| ' ' || LAST_NAME AS FULL_NAME
			 FROM        STUDENTS
			 WHERE       STUDENT_ID = '$user_student_id'"
			));
			
	if (AllowUse('Attendance/AddAbsences.php')) {
		$add_link = button('add',_('Add Absence')
				,"Modules.php"
				."?modname=Attendance/AddAbsences.php"
				."&search_modfunc=list"
				."&stuid=$user_student_id"
				);
	}

	$total_RET = DBGet(DBQuery(
			"SELECT      COALESCE((SUM(STATE_VALUE)),0.0) AS TOTAL 
			 FROM        ATTENDANCE_DAY ad 
			 WHERE       ad.STUDENT_ID = '$user_student_id' 
			 AND         ad.SCHOOL_DATE BETWEEN '$start_date' AND '$end_date'"
			));
			
	$absences_RET = DBGet(DBQuery(
			"SELECT      ap.STUDENT_ID
			 ,           ap.PERIOD_ID
			 ,           ap.SCHOOL_DATE
			 ,           ac.SHORT_NAME
			 ,           ac.STATE_CODE
			 ,           ad.STATE_VALUE
			 ,           ad.COMMENT 
			 FROM        ATTENDANCE_PERIOD ap
			 LEFT OUTER JOIN ATTENDANCE_DAY ad
			 ON          ap.STUDENT_ID = ad.STUDENT_ID
			 AND         ap.SCHOOL_DATE = ad.SCHOOL_DATE
			 LEFT OUTER JOIN ATTENDANCE_CODES ac
			 ON          ap.ATTENDANCE_CODE = ac.ID
			 WHERE (     ac.DEFAULT_CODE != 'Y'
			 OR          ac.DEFAULT_CODE IS NULL
			       ) 
			 AND         ap.STUDENT_ID = '$user_student_id'
			 AND         ap.SCHOOL_DATE BETWEEN '$start_date' AND '$end_date'
			 ORDER BY    ap.SCHOOL_DATE DESC"
			), array(), array('SCHOOL_DATE','PERIOD_ID'));

	$state_absence_counts['A'] = 0;
	$state_absence_counts['T'] = 0;
	$state_absence_counts['P'] = 0;

	foreach($absences_RET as $date=>$periods) {
		foreach($periods as $period=>$absence) {
			$absence = $absence[1];
			$absence_counts[$absence['SHORT_NAME']]++;
			$state_absence_counts[$absence['STATE_CODE']]++;
			$state_day_count[$absence['STATE_CODE']][$date] = true;
		}
	}

	$content = '<TABLE border="0" cellpadding="4" cellspacing="0">';
	$absent_content = '<B>'._('Absent').':</B> '
			.$state_absence_counts['A'].' '._('periods').' ('._('during').' '
			.(count($state_day_count['A'])).' '._('days').')<UL>';
			
	$present_content = '<B>'._('Other Marks').':</B> '
			.($state_absence_counts['P']+$state_absence_counts['T'])
			.' '._('periods').' ('._('during').' '
			.(count($state_day_count['T'])+count($state_day_count['P']))
			.' '._('days').')<UL>';
			
	foreach($codes_RET as $code) {
		$this_li = '<LI>'
					.'<SPAN style="background-color:'
					._getBgColorForStateCode($code['STATE_CODE']).'">&nbsp;'
					.$code['SHORT_NAME']
					.'&nbsp;</SPAN>&nbsp;&nbsp;'
					.$code['TITLE']
					.' : '
					.($absence_counts[$code['SHORT_NAME']] 
						? $absence_counts[$code['SHORT_NAME']] 
						: 0
					).' '._('periods')
					.'</LI>';

		switch ($code['STATE_CODE']) {
			case 'A':	$absent_content .= $this_li;	break;
			default:	$present_content .= $this_li;	break;
		}
	}

	//$present_content .= '<LI><SPAN style="background-color:#00FF00">-</SPAN>'._('No class scheduled this period');
	$absent_content .= '</UL>';
	$present_content .= '</UL>';

	$content .= 
			'<TR>'
			.'<TD valign="top">'.$absent_content.'</TD>'
			.'<TD valign="top">'.$present_content.'</TD>'
			.'</TR>'
			.'<TR>'
			.'<TD colspan="2">'.$days_content.'<BR>'
			.'<B>'._('Total Full Days Attended').': </B>'
			.$total_RET[1]['TOTAL']
			.'</TD>'
			.'</TR>'
			.'</TABLE>';
	
	DrawHeader($content,$add_link);
	$PHP_tmp_SELF = PreparePHP_SELF();

	$i = 0;
	foreach($absences_RET as $school_date=>$absences) {
		$i++;
		$days_RET[$i]['SCHOOL_DATE'] = ProperDate($school_date);
		$days_RET[$i]['DAILY'] = 
				_makeStateValue($absences[key($absences)][1]['STATE_VALUE']);
		$days_RET[$i]['COMMENT'] = $absences[key($absences)][1]['COMMENT'];
		foreach($absences as $period_id=>$absence) {
			$days_RET[$i][$period_id] = 
					_makeColor($absence[1]['SHORT_NAME']
					, $absence[1]['STATE_CODE']);
		}
	}
	
	$periods_RET = DBGet(DBQuery(
			"SELECT      PERIOD_ID
			 ,           SHORT_NAME 
			 FROM        SCHOOL_PERIODS 
			 WHERE       SCHOOL_ID = '$user_school' 
			 AND         SYEAR = '$user_syear' 
			 ORDER BY    SORT_ORDER"
			));
			
	$columns['SCHOOL_DATE'] = _('Date');
	$columns['DAILY'] = _('Present');
	$columns['COMMENT'] = _('Comment');
	
	foreach($periods_RET as $period) {
		$columns[$period['PERIOD_ID']] = $period['SHORT_NAME'];
	}
	if (count($days_RET)) {
		ListOutput($days_RET, $columns, _('Day'), _('Days'));
	}
	else {
		echo '<BR>';
		$content = $name_RET[1]['FULL_NAME'].
				_(' has no absences in this time period.').'  ';
		if ($user_profile == 'admin') {
			$content .= '<BR><BR>'.
					_('To search for other students, click the red "X" next to ')
					.$name_RET[1]['FULL_NAME']
					._("'s name in the Side menu");
		}
		echo DrawBlock(_('Note'),$content);
		echo '<pre>'.$content.'<pre>';
	}
}

function _makeStateValue($value) {
	global $THIS_RET,$date; //so, im trying to figure out why this is here -BL
	
	if ($value == '0.0') {
		return 'None';
	}
	elseif ($value == '.5') {
		return _('Half-Day');
	}
	else {
		return _('Full-Day');
	}
}

function _getBgColorForStateCode($state_code) {
	switch($state_code) {
		case 'P':	return '#00FF00';
		case 'A':	return '#FF0000';
		case 'T':	return '#FFCC00';
		default:	return '#0000FF';
	}
}

function _makeColor($value, $state_code) {
	global $codes_shortname_RET;

	$value = '<DIV onMouseOver=\'stm(["","'
			.$codes_shortname_RET[$value][1]['TITLE'].'"]'
			.',["white","#333366","","","",,"black",'
			.'"#e8e8ff","","","",,50,,2,"#333366",2,,,,,"",,,,]);\' "
			."onMouseOut=\'htm()\'> '.$value.' </DIV>';

	return '<TABLE cellspacing="0" bgcolor="'
			._getBgColorForStateCode($state_code)
			.'" class="innerLO_field">'
			.'<TR><TD> '.$value.' </TD></TR>'
			.'</TABLE>';
}
?>